package database;

import po.*;

import java.sql.*;
import java.util.ArrayList;

public class SalesService {
	private String username;
	private String password;
	private Connection conn;
	private String ConnectURL="jdbc:mysql://127.0.0.1:3306";
	private String Database="/obss";
	
	public SalesService(String username, String password) throws SQLException,ClassNotFoundException{
		this.username = username;
		this.password = password;
		Class.forName("com.mysql.jdbc.Driver");	
		
		conn=DriverManager.getConnection(ConnectURL+Database,username,password);
	}
	
	public String getDatabase(){return Database;}	
	public String getConnectURL(){return ConnectURL;}	
	public void setDatabase(String database){Database=database;}	
	public void setConnectURL(String URL){ConnectURL=URL;}
	
	
	public void makeNewSale(SalesPO salepo) throws SQLException{

			Statement stmt=conn.createStatement();
			
			int salesID=salepo.getSalesID();
			String username=salepo.getUsername();
			ArrayList<BookPO> books =salepo.getBooks();
			ArrayList<Integer> numbers = salepo.getNumbers();
			double totalPrice=salepo.getTotalPrice();
			double offsetQuota=salepo.getOffsetQuota();
			int offsetTicketNumber=salepo.getOffsetTicketNumber();
			double discountRate=salepo.getDiscountRate();
			boolean discountTicket=salepo.useDiscountTicket();	
			double realPrice=salepo.getRealPrice();
			
			String cmd="insert into salesinfo values("+salesID+",'"+username+"',"+totalPrice+","+offsetQuota+","+offsetTicketNumber+","+discountRate+","+discountTicket+","+realPrice+")";
			stmt.execute(cmd);
			
			for(int i=0;i<books.size();i++){
				Statement stmt2=conn.createStatement();				
				int bookID=books.get(i).getBookID();
				int booknum=numbers.get(i);
				String title=books.get(i).getTitle();
				String author=books.get(i).getAuthor();
				String press=books.get(i).getPress();
				String publishDate=books.get(i).getPublishDate();
				double price=books.get(i).getPrice();
				String category=books.get(i).getCategory();
				
				String cmd2="insert into salesbooks values("+salesID+","+bookID+","+booknum+",'"+title+"','"+author+"','"+press+"','"+publishDate+"',"+price+",'"+category+"')";				
				stmt2.execute(cmd2);	
				stmt2.close();
			}
					
			stmt.close();
			
		
		
		
		
		
	}
	

	public void deletSale(int salesID)throws SQLException{
			Statement stmt=conn.createStatement();
			String cmd="delete from salesinfo where salesID="+salesID;
			stmt.execute(cmd);
			
			Statement stmt2=conn.createStatement();
			String cmd2="delete from salesbooks where salesID="+salesID;
			stmt2.execute(cmd2);
			
			stmt.close();
			stmt2.close();
			
	}

	public void UpdateSale(SalesPO salepo)throws SQLException{
			Statement stmt=conn.createStatement();
			
			int salesID=salepo.getSalesID();
			String username=salepo.getUsername();
			ArrayList<BookPO> books =salepo.getBooks();
			ArrayList<Integer> numbers = salepo.getNumbers();
			double totalPrice=salepo.getTotalPrice();
			double offsetQuota=salepo.getOffsetQuota();
			int offsetTicketNumber=salepo.getOffsetTicketNumber();
			double discountRate=salepo.getDiscountRate();
			boolean discountTicket=salepo.useDiscountTicket();	
			double realPrice=salepo.getRealPrice();
			
			String cmd="update salesinfo set salesID="+salesID+",username='"+username+"',totalPrice="+totalPrice+",offsetQuota="+offsetQuota+",offsetTicketNumber="+offsetTicketNumber+",discountRate="+discountRate+",discountTicket="+discountTicket+",realPrice="+realPrice+" where salesID="+salesID;
			stmt.execute(cmd);
			
			for(int i=0;i<books.size();i++){
				Statement stmt2=conn.createStatement();				
				int bookID=books.get(i).getBookID();
				int booknum=numbers.get(i);
				String title=books.get(i).getTitle();
				String author=books.get(i).getAuthor();
				String press=books.get(i).getPress();
				String publishDate=books.get(i).getPublishDate();
				double price=books.get(i).getPrice();
				String category=books.get(i).getCategory();
				
				String cmd2="update salesbooks set salesID="+salesID+",bookID="+bookID+",booknum="+booknum+",title='"+title+"',author='"+author+"',press='"+press+"',publishDate='"+publishDate+"',price="+price+",category='"+category+"' where salesID="+salesID+" and bookID="+bookID;				
				stmt2.execute(cmd2);	
				stmt2.close();
			}
					
			stmt.close();
			
		
		
	}
	
	public SalesPO findSale(int salesID)throws SQLException{
			Statement stmt=conn.createStatement();
			String cmd="select *from salesinfo where salesID="+salesID;			
			ResultSet rs=stmt.executeQuery(cmd);			
			
			String username = null;
			ArrayList<BookPO> books =new ArrayList<BookPO>();
			ArrayList<Integer> numbers =new ArrayList<Integer>();
			double totalPrice = 0;
			double offsetQuota = 0;
			int offsetTicketNumber = 0;
			double discountRate = 0;
			boolean discountTicket = false;	
			double realPrice = 0;
			
			
			
			while(rs.next()){
				username=rs.getString(2);
				totalPrice=rs.getDouble(3);
				offsetQuota=rs.getDouble(4);
				offsetTicketNumber=rs.getInt(5);
				discountRate=rs.getDouble(6);
				discountTicket=rs.getBoolean(7);	
				realPrice=rs.getDouble(8);							
			}
			
			Statement stmt2=conn.createStatement();
			String cmd2="select *from salesbooks where salesID="+salesID;			
			ResultSet rs2=stmt2.executeQuery(cmd2);
		
			int bookID;
			int stock=0;
			int booknum;
			String title;
			String author;
			String press;
			String publishDate;
			double price;
			String category;			
			
			BookPO BPtemp;
			
			while(rs2.next()){
				bookID=rs2.getInt(2);
				booknum=rs2.getInt(3);
				title=rs2.getString(4);
				author=rs2.getString(5);
				press=rs2.getString(6);
				publishDate=rs2.getString(7);
				price=rs2.getDouble(8);
				category=rs2.getString(9);
				
				BPtemp=new BookPO(bookID,stock,title,author,
						press,publishDate,price,category);
				
				books.add(BPtemp);
				numbers.add(booknum);
				
			}
			
			SalesPO sp=new SalesPO(salesID,username,books,
					numbers,totalPrice,offsetQuota,
					offsetTicketNumber,discountRate,
					discountTicket,realPrice);
			
			
			
			stmt.close();
			rs.close();
			stmt2.close();
			rs2.close();
			return sp;
			
	
	}
	
	public int aimID() throws SQLException{
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("select * from salesinfo");
		int ID = 1;
		
		while(rs.next()){
			ID++;
		}
		
		
		return ID;
	}
}
